package org.tnc.budget.dao;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.tnc.budget.model.EventTable;

/**
 * 
 * @author houjj | 2011-4-4 下午04:03:16
 */
public class EventTableDaoHibernate extends
		GenericHibernateDAO<EventTable, Integer> {

	/**
	 * 根据指定的开始时间和结束时间查询所有用户的约会事件
	 * 
	 * @param startDate 开始日期，格式2001-1-2
	 * @param endDate 结束日期，格式2001-1-2
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<EventTable> findEvents(String startDate, String endDate) {
		List<EventTable> list = new ArrayList<EventTable>();

		String sql = "SELECT * FROM event_table WHERE TO_DAYS(start_date) >= TO_DAYS( ? ) AND TO_DAYS(end_date) <= TO_DAYS( ? ) AND is_valid = 1";
		Session sess = this.getSession();
		try {
			SQLQuery q = sess.createSQLQuery(sql).addEntity(EventTable.class);
			q.setParameter(0, startDate);
			q.setParameter(1, endDate);

			List l = q.list();
			if (l != null && l.size() > 0)
				list = l;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// sess.close();
		}
		return list;
	}
	
	/**
	 * 查询指定年月的所有用户约会数据
	 * @param year
	 * @param month
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<EventTable> findEventByMonth(int year, int month) {
		List<EventTable> list = new ArrayList<EventTable>();

		String sql = "SELECT * FROM event_table WHERE YEAR(end_date) = ? AND MONTH(end_date) = ? AND is_valid = 1";
		Session sess = this.getSession();
		try {
			SQLQuery q = sess.createSQLQuery(sql).addEntity(EventTable.class);
			q.setParameter(0, year);
			q.setParameter(1, month);

			List l = q.list();
			if (l != null && l.size() > 0)
				list = l;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// sess.close();
		}
		return list;
	}

	/**
	 * 查询指定年月的某个用户的数据
	 * @param year
	 * @param month
	 * @param name
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<EventTable> findEventByMonth(int year, int month, Long name) {
		List<EventTable> list = new ArrayList<EventTable>();

		String sql = "SELECT * FROM event_table WHERE YEAR(end_date) = ? AND MONTH(end_date) = ? AND event_people= ? AND is_valid = 1";
		Session sess = this.getSession();
		try {
			SQLQuery q = sess.createSQLQuery(sql).addEntity(EventTable.class);
			q.setParameter(0, year);
			q.setParameter(1, month);
			q.setParameter(2, name);

			List l = q.list();
			if (l != null && l.size() > 0)
				list = l;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// sess.close();
		}
		return list;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<EventTable> getUserEventByClientEventId(Long id, String clientId) {
		String sql = "SELECT * FROM event_table WHERE event_id = ? AND event_people = ? and is_valid = 1";
		Session sess = this.getSession();
		try {
			SQLQuery q = sess.createSQLQuery(sql).addEntity(EventTable.class);
			q.setParameter(0, clientId);
			q.setParameter(1, id);

			List l = q.list();
			if (l != null && l.size() > 0)
			{
				return l;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// sess.close();
		}
		
		return new ArrayList<EventTable>();
	}

	/**
	 * 更新用户的指定客户端ID的事件的状态，是否有效
	 * @param userid 用户ID
	 * @param ids 客户端ID数值
	 * @param isValid 是否有效
	 * @return
	 */
	public int updateEventTablesByClientIds(Long userid, String[] ids, boolean isValid) {
		
		StringBuffer idArr = new StringBuffer();
		for (int i = 0; i < ids.length; i++) {
			idArr.append("'").append(ids[i]).append("'");
			if((i+1) < ids.length )
				idArr.append(",");
		}
		
		String sql = "UPDATE event_table SET is_valid="+(isValid ? 1 : 0)+" WHERE event_people="+userid.longValue()+" AND event_id IN ("+idArr.toString()+") AND (bc_mgr_reviewed is null or bc_mgr_review_passed=0)";
		Session sess = this.getSession();
		try {
			SQLQuery q = sess.createSQLQuery(sql).addEntity(EventTable.class);
			
			int num = q.executeUpdate();
			return num;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// sess.close();
		}
		
		return 0;
	}
}
